package com.ipworkbench.common.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.ipworkbench.common.util.IPWException;

/**
 * JDBC封装类（DAO层父类）
 * 
 * @author zhangfaheng
 * 
 * @date Oct 27, 2009
 */
public class JdbcUtil extends BaseDaoSupport {

	private QueryRunner queryRunner;

	/**
	 * 执行非查询sql语句（添加、修改、删除），需在业务层启用事务
	 * 
	 * @param sql
	 *            sql语句
	 * 
	 * @param params
	 *            参数数组
	 * 
	 * @return 受影响的行数
	 */
	public int update(String sql, Object... params) throws IPWException {
		int affectedRows = 0;
		queryRunner = new QueryRunner();

		try {
			Connection conn = getTranConn();
			if (null == params || params.length == 0) {
				affectedRows = queryRunner.update(conn, sql);
			} else {
				affectedRows = queryRunner.update(conn, sql, params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		}
		return affectedRows;
	}

	/**
	 * 执行批量非查询sql语句（添加、修改、删除），需在业务层启用事务
	 * 
	 * @param sql
	 *            sql语句
	 * 
	 * @param params
	 *            二维参数数组
	 * 
	 * @return 受影响的行数的数组
	 */
	public int[] batchUpdate(String sql, Object[][] params) throws IPWException {
		queryRunner = new QueryRunner();
		int[] affectedRows = new int[0];
		try {
			Connection conn = getTranConn();

			affectedRows = queryRunner.batch(conn, sql, params);
		} catch (SQLException e) {
			throw new IPWException(e);
		}
		return affectedRows;
	}

	/**
	 * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中
	 * 
	 * @param sql
	 *            sql语句
	 * 
	 * @param params
	 *            参数数组
	 * 
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> find(String sql, Object... params)
			throws IPWException {
		queryRunner = new QueryRunner();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Connection conn = null;
		try {

			conn = getConn();
			if (params == null) {
				list = (List<Map<String, Object>>) queryRunner.query(conn, sql,
						new MapListHandler());
			} else {
				list = (List<Map<String, Object>>) queryRunner.query(conn, sql,
						new MapListHandler(), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	/**
	 * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中
	 * 
	 * @param sql
	 *            sql语句
	 * 
	 * @param params
	 *            参数数组
	 * 
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> find(String sql, String columnName, Object... params)
			throws IPWException {
		queryRunner = new QueryRunner();
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		try {
			conn = getConn();
			if (params == null) {
				list = (List<T>) queryRunner.query(conn, sql,
						new ColumnListHandler(columnName));
			} else {
				list = (List<T>) queryRunner.query(conn, sql,
						new ColumnListHandler(columnName), params);
			}
		} catch (Exception e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	/**
	 * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
	 * 
	 * @param entityClass
	 *            类名
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数数组
	 * @return 查询结果
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> find(Class<T> entityClass, String sql, Object... params)
			throws IPWException {

		queryRunner = new QueryRunner();
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		try {
			conn = getConn();
			if (params == null) {
				list = (List<T>) queryRunner.query(conn, sql,
						new BeanListHandler(entityClass));
			} else {
				list = (List<T>) queryRunner.query(conn, sql,
						new BeanListHandler(entityClass), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	/**
	 * 查询出结果集中的第一条记录，并封装成对象
	 * 
	 * @param entityClass
	 *            类名
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数数组
	 * @return 对象
	 */
	@SuppressWarnings("unchecked")
	public <T> T findFirst(Class<T> entityClass, String sql, Object... params)
			throws IPWException {

		queryRunner = new QueryRunner();
		Object object = null;
		Connection conn = null;

		try {

			conn = getConn();
			if (params == null || params.length == 0) {
				object = queryRunner.query(conn, sql, new BeanHandler(
						entityClass));
			} else {
				object = queryRunner.query(conn, sql, new BeanHandler(
						entityClass), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return (T) object;
	}

	/**
	 * 查询出结果集中的第一条记录，并封装成Map对象
	 * 
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数数组
	 * @return 封装为Map的对象
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> findFirst(String sql, Object... params)
			throws IPWException {

		queryRunner = new QueryRunner();
		Map<String, Object> map = null;
		Connection conn = null;

		try {
			conn = getConn();
			if (params == null) {
				map = (Map<String, Object>) queryRunner.query(conn, sql,
						new MapHandler());
			} else {
				map = (Map<String, Object>) queryRunner.query(conn, sql,
						new MapHandler(), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return map;
	}

	/**
	 * 查询某一条记录，并将指定列的数据转换为Object
	 * 
	 * @param sql
	 *            sql语句
	 * @param columnName
	 *            列名
	 * @param params
	 *            参数数组
	 * @return 结果对象
	 */
	public Object findBy(String sql, String columnName, Object... params)
			throws IPWException {

		queryRunner = new QueryRunner();
		Object object = null;
		Connection conn = null;

		try {
			conn = getConn();
			if (params == null) {
				object = queryRunner.query(conn, sql, new ScalarHandler(
						columnName));
			} else {
				object = queryRunner.query(conn, sql, new ScalarHandler(
						columnName), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return object;
	}

	/**
	 * 查询某一条记录，并将指定列的数据转换为Object
	 * 
	 * @param sql
	 *            sql语句
	 * @param columnIndex
	 *            列索引
	 * @param params
	 *            参数数组
	 * @return 结果对象
	 */
	public Object findBy(String sql, int columnIndex, Object... params)
			throws IPWException {

		queryRunner = new QueryRunner();
		Object object = null;
		Connection conn = null;

		try {
			conn = getConn();
			if (params == null) {
				object = queryRunner.query(conn, sql, new ScalarHandler(
						columnIndex));
			} else {
				object = queryRunner.query(conn, sql, new ScalarHandler(
						columnIndex), params);
			}
		} catch (SQLException e) {
			throw new IPWException(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
		return object;
	}

	/**
	 * 执行非查询的SQL语句,未关闭数据路资源
	 * 
	 * @param sql
	 * 
	 * @return boolean
	 */

	protected boolean executeSql(String sql) throws IPWException {
		boolean bool = false;
		int count = 0;
		try {

			ps = cn.prepareStatement(sql);
			count = ps.executeUpdate();
			if (count > 0) {
				bool = true;
			}
		} catch (SQLException e) {

			throw new IPWException(e.getMessage());
		}
		return bool;
	}

	/**
	 * 执行非查询的SQL语句,未关闭数据路资源(Statement)
	 * 
	 * @param sql
	 * 
	 * @return boolean
	 */

	protected boolean executeSqlForSt(String sql) throws IPWException {
		boolean bool = false;
		int count = 0;
		try {

			st = cn.createStatement();
			count = st.executeUpdate(sql);
			if (count > 0) {
				bool = true;
			}
		} catch (SQLException e) {

			throw new IPWException(e.getMessage());
		}
		return bool;
	}

	/**
	 * 执行非查询的SQL语句，如update,delete,insert等(Statement)
	 * 
	 * @param sql
	 * @return boolean
	 * @throws IPWException
	 */
	protected boolean executeForSt(String sql) throws IPWException {
		super.takeConn(false);
		try {
			return executeSqlForSt(sql);
		} catch (Exception e) {
			throw new IPWException(e.getMessage());
		} finally {
			super.closeDB();
		}
	}

	/**
	 * 返回多行单列记录
	 * 
	 * @param sql
	 * 
	 * @param params
	 * 
	 * @return
	 * 
	 * @throws MPSException
	 */
	protected List<Object> queryOneList(String sql, Object... params)
			throws IPWException {
		List<Object> list = new ArrayList<Object>();
		try {
			super.takeConn(false);
			if (null == cn) {
				return list;
			}
			ps = cn.prepareStatement(sql);

			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			if (null == rs) {
				return list;
			}
			while (rs.next()) {
				list.add(rs.getObject(1));
			}
			return list;
		} catch (Exception e) {
			throw new IPWException(e.getMessage());
		} finally {
			super.closeDB();
		}
	}

	/**
	 * 执行数据库查询，返回数据表
	 * 
	 * @param sql
	 * 
	 * @param params
	 *            SQL语句里的参数
	 * 
	 * @return ArrayList
	 * 
	 * @throws SQLException
	 * 
	 */
	protected ArrayList<HashMap<String, Object>> queryList(String sql,
			Object... params) throws IPWException {
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		try {
			super.takeConn(false);
			if (null == cn) {
				return list;
			}
			ps = cn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			if (null == rs) {
				return list;
			}
			ResultSetMetaData meta = rs.getMetaData();
			int columnNumber = meta.getColumnCount();

			while (rs.next()) {
				HashMap<String, Object> hm = new HashMap<String, Object>();
				for (int i = 1; i <= columnNumber; i++) {
					hm.put(meta.getColumnName(i), rs.getString(i));
				}
				list.add(hm);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new IPWException(e.getMessage());
		} finally {
			super.closeDB();
		}
		return list;
	}

	/**
	 * 查询单个字段信息，返回字符串
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws IPWException
	 */
	protected String queryToString(String sql, Object... params)
			throws IPWException {
		String str = null;
		try {
			super.takeConn(false);
			if (null == cn) {
				return str;
			}
			ps = cn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			rs = ps.executeQuery();
			if (null == rs) {
				return str;
			}
			while (rs.next()) {
				str = rs.getString(1);
			}
			return str;
		} catch (Exception e) {
			throw new IPWException(e.getMessage());
		}
	}

	/**
	 * 执行数据库查询，返回一行数据
	 * 
	 * @param sql
	 * 
	 * @param params
	 *            SQL语句里的参数
	 * 
	 * @return HashMap
	 */
	protected HashMap<String, Object> queryOne(String sql, Object... params)
			throws IPWException {
		HashMap<String, Object> hm = new HashMap<String, Object>();
		try {

			ArrayList<HashMap<String, Object>> list = queryList(sql, params);
			if (list.size() > 0) {
				hm = (HashMap<String, Object>) list.get(0);
			}
			return hm;
		} catch (Exception e) {
			throw new IPWException(e.getMessage());
		}

	}

	/**
	 * 以事务的方式批量执行非查询的SQL语句，如update,delete,insert等
	 * 
	 * @param sql
	 * 
	 * @return boolean
	 * 
	 * @throws IPWException
	 */
	protected boolean[] execute(String[] sqlList) throws IPWException {
		boolean[] bool = new boolean[sqlList.length];
		int i = 0;

		super.takeConn(false);
		try {
			for (String sql : sqlList) {
				if (sql == null || sql.equals(""))
					continue;
				bool[i++] = executeSql(sql);
			}

		} catch (Exception e) {

			throw new IPWException(e.getMessage());
		} finally {
		}

		return bool;
	}

	
	/**
	 * 
	 * 批量执行SQL
	 * 
	 * @param sql 参数sql，是由一些sql语句拼起来的，用;隔开。
	 * @return
	 * @throws IPWException
	 */
	protected int[] doBatch(String sql) throws IPWException {
		int[] rowResult = null;
		String a;
		try {
			cn = getTranConn();
			st = cn.createStatement();
			StringTokenizer stringTokenizer = new StringTokenizer(sql, ";");
			while (stringTokenizer.hasMoreTokens()) {
				a = stringTokenizer.nextToken();
				st.addBatch(a);
			}
			rowResult = st.executeBatch();
		} catch (Exception e) {
			throw new IPWException(e);
		}
		return rowResult;
	}

	/**
	 * 获取数据字典值
	 * 
	 * @param dictTypeID
	 * @return
	 * @throws IPWException
	 */
	public HashMap<String, Object> getDict(long dictTypeID) throws IPWException {

		HashMap<String, Object> map = new LinkedHashMap<String, Object>();

		String sql = "select t.item_name,t.item_code from t_element_item t where t.type_id= ? ";
		try {
			for (Map<String, Object> m : find(sql, new Object[] { dictTypeID })) {
				map.put("" + m.get("item_code"), m.get("item_name"));
			}
		} catch (Exception e) {
			throw new IPWException(e.getMessage());
		}
		return map;
	}

}
